package cn.strategy.fund;

import cn.math.MyStatistics;
import org.checkerframework.checker.units.qual.A;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 判断买点
 * */
public class Mai_dian_case {

    @Test
    public void show_mai_dian() throws ClassNotFoundException, SQLException {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:~/data;AUTO_SERVER=TRUE", "root", "root");

        String sql = "SELECT symbol FROM type_stock GROUP BY symbol";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            //getData_ex(conn, rs.getString(1));
            getLastPrice(conn, rs.getString(1));
        }

        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        if (conn != null) conn.close();
    }

    public void getLastPrice(Connection conn, String symbol) {
        String sql = "SELECT PRICE, NAME, ZHANGDIE FROM df_stock_date WHERE SYMBOL = '"+ symbol +"' ORDER BY STOCK_DATE DESC LIMIT 0, 1";

        PreparedStatement stmt = null;
        ResultSet rs = null;
        Float price = 0F;

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                price = rs.getFloat(1);
                getLastPrice(conn, symbol, rs.getString(2), price, rs.getFloat(3));
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void getLastPrice(Connection conn, String symbol, String name, Float price, Float zhangdie) {
        String sql = "SELECT PRICE, STOCK_DATE FROM mai_dian WHERE SYMBOL = '"+ symbol +"' and h = 0 ORDER BY STOCK_DATE DESC";

        PreparedStatement stmt = null;
        ResultSet rs = null;
        //List<Float> lst = new ArrayList<>();

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
//                Double vm = get_mean(conn, symbol, rs.getString(2));
//                if (vm < 0.2) {
//                    break;
//                }
                Float f = rs.getFloat(1);
                Float v = f / price;
                if (v < 1.01 && v > 0.99 && zhangdie > 0F && zhangdie < 0.5F) {
                //if (v < 1.01 && v > 0.99 && zhangdie > -1.5F && zhangdie < -0.5F) {
                    System.out.println(symbol + " " + name + " " + rs.getString(2));
                }
//                if (vm > 0.1 && vm < 0.2) {
//                    Float f = rs.getFloat(1);
//                    Float v = f / price;
//                    if (v < 1.01 && v > 0.99 && zhangdie > -1.5F && zhangdie < -0.5F) {
//                        System.out.println(symbol + " " + name + " " + rs.getString(2));
//                    }
//                }
                break;
            }

//            if (lst.size() > 0) {
//                Float f = lst.get(0);
//                Float v = f / price;
//                if (v < 1.01 && v > 0.98) {
//                    System.out.println(symbol + " " + name);
//                }
//            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public static Double get_mean(Connection conn, String symbol, String STOCK_DATE) {
        String sql = "SELECT PRICE, STOCK_DATE FROM df_stock_date WHERE SYMBOL = '" + symbol + "' AND STOCK_DATE >= '" + STOCK_DATE + "' ORDER BY STOCK_DATE DESC";

        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Double> lst = new ArrayList<>();

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                //price = rs.getFloat(1);
                lst.add(rs.getDouble(1));
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return MyStatistics.my_mean(lst);
    }
}



















































